﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using log4net;

namespace SHKurzy.Upsize
{
	/// <summary>
	/// 
	/// </summary>
	public class UpsizeHelper
	{
		private static readonly ILog _log = LogManager.GetLogger(typeof(UpsizeHelper));

		// nekde pouzivaji i @"Provider=Microsoft.ACE.OLEDB.12.0;...
		private readonly string _connectionStr = @"Provider = Microsoft.Jet.OLEDB.4.0; Data Source ={0}";

		private OleDbConnection _oleDbConnection;

		/// <summary>
		/// 
		/// </summary>
		/// <param name="filename">jmeno MS Access databazoveho souboru</param>
		public UpsizeHelper(string filename)
		{
			_log.Info("UpsizeHelper");

			TryOpenDatabase(filename);
		}



		private void TryOpenDatabase(string filename)
		{
			_log.Info("TryOpenDatabase()");
			
			var connectionStr = string.Format(_connectionStr, filename);
			_log.Info("connectionStr: " + connectionStr);

			_oleDbConnection = new OleDbConnection(connectionStr);
			_oleDbConnection.Open();
		}


		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		public List<string> GetTableList()
		{
			_log.Info("GetTables()");

			// Get the data table containing the schema
			var tables = _oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
			//These few lines of code can give you all system tables schema now if you want to have only tables created by the user then you have to put a filter on data of your DataTable dt.
			if (tables != null)
			{
				return (from DataRow p in tables.Rows
						where p["TABLE_TYPE"].ToString() == "TABLE"
						select p["TABLE_NAME"].ToString()).ToList();
			}

			return null;
		}



		/// <summary>
		/// nacteni ms access tabulky do DataTable
		/// </summary>
		/// <param name="table">vysledek</param>
		/// <returns></returns>
		public DataTable GetTable(string table)
		{
			var sql = string.Format("select * from [{0}] ", table);
			var oleDbCommand = new OleDbCommand(sql, _oleDbConnection);
			var oleDbDataAdapter = new OleDbDataAdapter(oleDbCommand);
			var result = new DataTable();
			oleDbDataAdapter.Fill(result);
			return result;
		}





	}
}
